Query Options (Optimizations)

Meeting the Query Challenge

Given the vast number of query formations possible within MDX, together with an almost endless number of cube designs and calculations, there arises a significant challenge in designing the fastest possible automated query. To assist the user in achieving the fastest possible query results, BI has developed a set of optimization switches that can be used to tweak the manner in which a query is executed.

Optimization Switches

To turn ON all query optimization switches, click the top half of the Query Options icon in the Query Ribbon.

To control individual optimization switches, click the bottom half of the Query Options icon in the Query Ribbon.

Default Optimization Switch Settings

The default values of the various optimization switches are shown below.

Switch	Default Value	Comment
Optimize Columns	OFF
Optimize Rows	OFF
Measure Optimization	ON	Can remain ON at all times.
Optimize Totals	OFF
Context Heuristics	ON	Can remain ON at all times.
Sub Query Mode	OFF	Default value may be changed in User Options.
Filtering Data	OFF	See Optimize checkbox in Data Filter dialog.
N-of-N Iterations	OFF	See Optimize checkbox in N-of-N dialog.

Optimization Scenarios

Quick Fix Strategy

When a slow query is encountered, it is possible to click the top half of the “Query Options’” button in order to enable all optimization switches. In many cases, the query speed will improve immediately. If performance does not improve, we recommend that you turn OFF the various optimization switches one-by-one until the optimal solution is found for the query.

Multiple Hierarchies from Different Dimensions

When using two or more hierarchies from DIFFERENT dimensions on a single query axis (either columns or rows), it is recommended to use the optimization switches that are based on the NONEMPTY function.

Multiple Hierarchies from Same Dimension

When using two or more hierarchies from the SAME dimension on a specific axis (column or row), it is recommended to perform optimization using the Context Heuristics optimization switch.

Multiple Elements from a Single Hierarchy

When using multiple elements from a single hierarchy, it is recommended to perform optimization using the Sub Query Mode.

Additional Optimization Tips

Turn OFF Query Pre-Check

The Query Pre-Check feature in the Query Ribbon is designed to protect users from accidentally running large queries and thus depleting server resources. The pre-check measures the size of the possible rows and columns in the result set and blocks overly large queries from executing. In some instances the Query Pre-Check can add significantly to calculation time and should be turned OFF to improve query response time.

Choose Flat Grid Type

BI Office allows users to choose from a number of different grid types. For large queries, the Flat Grid will deliver the shortest rendering time (while the Multi-Tier Grid will usually produce the longest rendering time).

Build Better Aggregation for OLAP Cubes

For OLAP cubes only, you can build better aggregations by means of Usage Based Optimization in SSAS. By sampling one or more queries against your cube in the Analysis Services, you will be able to use the sampled data to improve aggregation design for the cube. Applying these aggregations can dramatically improve the performance of your cube, since long running (but high demand) queries are optimized inside the cube’s structure.